The "database" for all the information about a cycle of senior projects is project information spreadsheet. It has been named Project Information ssss-eeee where ssss and eeee are the designations for the first and second terms of senior project, respectively. The information in this Excel file includes the following information which is often contained on an individual worksheet. There are 13 worksheets in the project information workbook.
The information contained on these worksheets often provides the data for mail merges to sponsors, coaches, and teams.
All of the worksheets do not need to be filled, and in many cases, and not be filled in, at the start of a project cycle. The information on different worksheets will be come relevant as a project cycle continues. The general flow through the worksheets in terms of the relative order in which worksheets become relevant and may need to be filled out with information is:
There are three Excel features that the project information workbook uses extensively and you will need to be familiar with to work with the workbook. These are:
User defined functions are a set of Visual Basic for Application functions that provide computation support for the worksheets. There are 25 of them defined as of May 2019. A few of them may be lava flow anti-patterns in that they are no longer actively used in the workbook. To view the functions, you need to have the Developer tab enabled in the toolbar ribbon (File -> Options -> Customize Ribbon -> Customize the Ribbon for Main Tabs).
Named regions provide a way to identify ranges of cells by a descriptive name. These names are used in cell formulas and the UDFs. When a name is used within a formula, the range of cells it references is not subject to automatic adjustment of the cell references when the formula is copied to other cells. This is a huge time saver when you are entering cell formulas that make use of the same data in different cells. When you do a mail merge, you can select the merge data using a named region which saves having to exactly copy the cell range.
Named regions are relatively easy to work with. There is a good named region tutorial here.
When you setup elements of the project information workbook during each project cycle you will most likely start with the workbook from a previous project cycle. You need to adjust the size of many named regions to accommodate the number of proposals, project, students, coaches, etc. in the current project cycle. You will do this using the Name Manager (Formulas -> Name Manager or Ctrl-F3). As you move through the term, you will be setting up different worksheets within the project information workbook. There may be one or more named regions on the worksheet. Once you have the data setup on the worksheet, adjust the named regions using the Name Manager. In the Name Manager dialog, you can sort by the Refers To field to find all of the named regions on the worksheet that you are setting up.
Array formulas is a feature that saves time and also reduces errors when entering cell formulas. They provide a way for one cell formula to return values for multiple cells on a worksheet. There is a good array formula tutorial here and some other examples and guidelines.
Unfortunately, there are a number of features/quirks/annoyances of array formulas that you have to get used to when working with them. Here are some of those features:
{}. If you put the cursor in the formula bar to edit the formula, those braces disappear.